package com.kaikeba.dao.impl;

import com.kaikeba.bean.Express;
import com.kaikeba.dao.ExpressDao;
import com.kaikeba.exception.CodeException;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressDaoImpl implements ExpressDao {
    /**
     * 用户返回控制台中所需的数据
     * [{总数：xxx，今日新增：xxx}，{待取总数：xxx，待取今日新增数：xxx}]
     *
     * @return
     */
    @Override
    public List<Map<String, Integer>> console() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Map<String,Integer>> list = new ArrayList<>();
        try {
            statement = conn.prepareStatement("select \n" +
           "COUNT(id) data1_sum,COUNT(TO_DAYS(intime) =TO_DAYS(NOW()) and `status`=1 or null) data1_new,\n" +
           "COUNT(`status`=0 or null) data2_sum,COUNT(TO_DAYS(intime) =TO_DAYS(NOW()) or null) data2_new\n" +
           "from express");

             resultSet = statement.executeQuery();
            while (resultSet.next()){
                int data1_sum = resultSet.getInt("data1_sum");
                int data1_new = resultSet.getInt("data1_new");
                int data2_sum = resultSet.getInt("data2_sum");
                int data2_new = resultSet.getInt("data2_new");
                Map<String,Integer> map1 = new HashMap<>();
                map1.put("data1_sum",data1_sum);
                map1.put("data1_new",data1_new);
                Map<String,Integer> map2 = new HashMap<>();
                map2.put("data2_sum",data2_sum);
                map2.put("data2_new",data2_new);
                list.add(map1);
                list.add(map2);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,statement,resultSet);
        }
        return list;
    }

    /**
     * 查找全部快递并根据参数判断是否分页
     *
     * @param limit      是否分页 true（默认）分页  false 不分页
     * @param offset     起始索引
     * @param pageNumber 单页显示数量
     * @return
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet result = null;
        List<Express> list = new ArrayList<>();
        try {
            if (limit){
                //需要分页
                statement = conn.prepareStatement("select * from express limit ?,?");
                statement.setInt(1,offset);
                statement.setInt(2,pageNumber);
            }else {
                //不需要分页
                statement = conn.prepareStatement("select * from express");
            }

            result = statement.executeQuery();
            while (result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                list.add(e);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,statement,result);
        }

        return list;
    }

    /**
     * 根据单号查询快递信息
     *
     * @param number 快递单号
     * @return
     */
    @Override
    public Express findByNumber(String number) {

        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet result = null;

        try {

            statement = conn.prepareStatement("select * from express where number=?");
            statement.setString(1,number);
            result = statement.executeQuery();
            while (result.next()){
                int id = result.getInt("id");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,statement,result);
        }

        return null;
    }

    /**
     * 根据取件码查询快递信息
     *
     * @param code 取件码
     * @return
     */
    @Override
    public Express findByCode(String code) {

        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet result = null;

        try {

            statement = conn.prepareStatement("select * from express where code=?");
            statement.setString(1,code);
            result = statement.executeQuery();
            while (result.next()){
                int id = result.getInt("id");
                String username = result.getString("username");
                String number = result.getString("number");
                String userPhone = result.getString("userPhone");
                String company = result.getString("company");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,statement,result);
        }

        return null;
    }

    /**
     * 根据用户电话号码查询快递信息
     *
     * @param userPhone 用户电话号码
     * @return
     */
    @Override
    public List<Express>  findByUserPhone(String userPhone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet result = null;
        List<Express> list = new ArrayList<>();
        try {
            statement = conn.prepareStatement("select * from express where userPhone=?");
            statement.setString(1,userPhone);
            result = statement.executeQuery();
            while (result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");

                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                list.add(e);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,statement,result);
        }
        return list;
    }

    /**
     * 根据用户电话号码和状态查询对应状态的快递信息（取件或待取）
     *
     * @param userPhone 用户电话号码
     * @param status    快递状态码
     * @return
     */
    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet result = null;
        List<Express> list = new ArrayList<>();
        try {
            statement = conn.prepareStatement("select * from express where userPhone=? and status=?");
            statement.setString(1,userPhone);
            statement.setInt(2,status);
            result = statement.executeQuery();
            while (result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                String sysPhone = result.getString("sysPhone");

                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                list.add(e);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,statement,result);
        }
        return list;
    }

    /**
     * 根据录入人的手机号，查询快递单号
     *
     * @param sysPhone 录入人手机号
     * @return
     */
    @Override
    public List<Express> findAllBySysPhone(String sysPhone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet result = null;
        List<Express> list = new ArrayList<>();
        try {
            statement = conn.prepareStatement("select * from express where sysPhone=?");
            statement.setString(1,sysPhone);
            result = statement.executeQuery();
            while (result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");


                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                list.add(e);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,statement,result);
        }
        return list;
    }

    /**
     * 快递录入
     *
     * @param e 录入的快递对象
     * @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insert(Express e) throws CodeException {

        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;

        try {
            statement = conn.prepareStatement("insert into express values (null,?,?,?,?,?,NOW(),null,0,?)");
            statement.setString(1,e.getNumber());
            statement.setString(2,e.getUserName());
            statement.setString(3,e.getUserPhone());
            statement.setString(4,e.getCompany());
            statement.setString(5,e.getCode());
            statement.setString(6,e.getSysPhone());
            return  statement.executeUpdate()>0?true:false;

        } catch (SQLException e1) {

            if (e1.getMessage().endsWith("for key 'code'")){
                CodeException e2  = new CodeException(e1.getMessage());
                throw e2;
            }else {
                e1.printStackTrace();
            }
        }finally {
            DruidUtil.close(conn,statement,null);
        }

        return false;
    }

    /**
     * 快递的修改
     *
     * @param id         要修改的快递id
     * @param newExpress 新的快递对象（number，company,username,status）
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean update(int id, Express newExpress) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            state = conn.prepareStatement("update express set number=?,company=?,username=?,userphone=?,status=? where id =?");
            state.setString(1,newExpress.getNumber());
            state.setString(2,newExpress.getCompany());
            state.setString(3,newExpress.getUserName());
            state.setString(4,newExpress.getUserPhone());
            state.setInt(5,newExpress.getStatus());
            state.setInt(6,id);

            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }

        return false;
    }

    /**
     * 快递删除
     *
     * @param id 需要删除的快递id
     * @return
     */
    @Override
    public boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            state = conn.prepareStatement("delete from express where id = ?");
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }

        return false;
    }

    /**
     * 更改快递的状态为1，设置取出时间，并设置取件码为空，表示取件完成
     *
     * @param code 要修改的快递取件码
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean updateStatus(String code) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            state = conn.prepareStatement("update express set status=1,outtime=NOW(),code=null where code=?");
            state.setString(1,code);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
